using System;
using System.Collections.Generic;
using OfficeOpenXml;

class MySQLParser
{
    const int START_COL = 2;
    const int TABLE_NAME_COL = 3;
    const int TABLE_LENGTH = 8;
    const string PRIMARY_KEY_TEMPLATE = "";
    const string TABLE_END_TEMPLATE = "";
    public bool isEnd(int startRow, ExcelWorksheet sheet)
    {
        if (sheet.Cells[startRow + 3, START_COL].Value == null) return true;
        return String.IsNullOrWhiteSpace(sheet.Cells[startRow + 3, START_COL].Value.ToString());
    }

    public int ReadTable(int startRow, ref ExcelWorksheet sheet)
    {
        var rowCursor = startRow;
        var tableLogicName = sheet.Cells[startRow, START_COL].Value;
        var tableName = sheet.Cells[startRow, TABLE_NAME_COL].Value;
        sheet.Cells[rowCursor, START_COL + TABLE_LENGTH + 1].Value = $"CREATE TABLE `{tableName}` (";
        rowCursor += 2;
        List<string> pks = new List<string>();
        while (!String.IsNullOrWhiteSpace(sheet.Cells[rowCursor, START_COL].Text))
        {
            var row = (object[,])sheet.Cells[rowCursor, START_COL, rowCursor, START_COL + TABLE_LENGTH - 1].Value;
            if (this.isPk(row))
            {
                pks.Add($"`{row[0, 1]}`");
            }
            sheet.Cells[rowCursor - 1, START_COL + TABLE_LENGTH + 1].Value = this.ReadRow(row);
            rowCursor += 1;
        }
        var singlePk = pks.Count <= 1;
        sheet.Cells[rowCursor - 1, START_COL + TABLE_LENGTH + 1].Value = $"  PRIMARY KEY ({String.Join(',', pks)})";
        var hasIncCol = (object[,])sheet.Cells[startRow + 2, 6, rowCursor - 1, 6].Value;
        sheet.Cells[rowCursor, START_COL + TABLE_LENGTH + 1].Value = $") ENGINE={(singlePk ? "InnoDB" : "MyISAM")} {(this.HasAutoIncrement(hasIncCol) ? "AUTO_INCREMENT=1" : "")} DEFAULT CHARSET=utf8mb4 COMMENT='{tableLogicName}'";
        return rowCursor + 2;
    }

    public string ReadRow(object[,] row)
    {
        bool isIncrement = false;
        if (row[0, 4] != null)
        {
            isIncrement |= Boolean.Parse(row[0, 4].ToString());
        }
        bool defaultNull = true;
        if (row[0, 5] != null)
        {
            defaultNull &= Boolean.Parse(row[0, 5].ToString());
        }
        string defaultValue = row[0, 3] == null ? String.Empty : row[0, 3].ToString();
        if (!defaultValue.ToLower().Equals("null") && !defaultValue.ToLower().Equals("current_timestamp"))
        {
            if (row[0, 2] != null && row[0, 2].Equals("bit(1)"))
            {
                defaultValue = $"b'{defaultValue}'";
            }
            else
            {
                defaultValue = $"'{defaultValue}'";
            }
        }
        return $"  `{row[0, 1]}` {row[0, 2]} {(defaultNull ? $"DEFAULT {defaultValue}" : "NOT NULL")}{(isIncrement ? " AUTO_INCREMENT" : "")} COMMENT '{row[0, 0]}{(row[0, 7] != null ? $":{row[0, 7]}" : "")}',";
    }

    public bool isPk(object[,] row)
    {
        if (row[0, 6] == null)
        {
            return false;
        }
        return Boolean.Parse(row[0, 6].ToString());
    }

    public bool HasAutoIncrement(object[,] col)
    {
        for (int i = 0; i < col.GetLength(0); i++)
        {
            if (col[i, 0] == null) return false;
            if (Boolean.Parse(col[i, 0].ToString())) return true;
        }
        return false;
    }
}

